Release 10.1A: OpenEdge Data Management:
Database Administration

Compacting indexes

When the DBANALYS utility indicates that space utilization of an index is reduced to 60% or less, use the PROUTIL IDXCOMPACT utility to perform index compaction online. Performing index compaction increases space utilization of the index block to the compacting percentage specified. For example:

proutil db-name -C idxcompact [ owner-name.]table-name.index-name [ n ]

Note: For the complete syntax description, see Chapter 20, " PROUTIL Utility."

Performing index compaction reduces the number of blocks in the B-tree and possibly the number of B-tree levels, which improves query performance.

The index compacting utility operates in phases:

Phase 1 — If the index is a unique index, the delete chain is scanned and the index blocks are cleaned up by removing deleted entries.
Phase 2 — The nonleaf levels of the B-tree are compacted starting at the root working toward the leaf level.
Phase 3 — The leaf level is compacted.

The _UserStatus virtual system table displays the utility’s progress.

Note: Because index compacting is performed online, other users can use the index simultaneously for read or write operation with no restrictions. Index compacting only locks 1 to 3 index blocks at a time, for a short time. This allows full concurrency.